#include<cctype>
#include<sstream>

#include"db_connection.h"
#include"generic_table.h"

using namespace std;

namespace uhdpbs {
	namespace db {
		connection::connection() {
		}
		connection::~connection() {
			if(pMysqlConnection)
				this->close();
		}

		bool connection::open(string host, string database, string user, string password) {
			string sql = "SET sql_mode = 'ANSI_QUOTES';";
			
			pMysqlConnection = mysql_init(NULL);
			if(mysql_real_connect(pMysqlConnection, host.c_str(), user.c_str(), password.c_str(), database.c_str(), 0, NULL, CLIENT_TRANSACTIONS))
				if(!mysql_real_query(pMysqlConnection, sql.c_str(), sql.length()))
					return true;

			string temp = mysql_error(pMysqlConnection);
			if(temp.length() > 0)
				sError += "\n\n" + temp;

			return false;
		}
		void connection::close() {
			mysql_close(pMysqlConnection);
			pMysqlConnection = NULL;
		}

		unsigned long connection::load(table* request, string order) {
			sError = "";
			
			// load meta data if missing
			load_meta_data(request);

			// create where clause
			// SELECT * FROM tbl WHERE (row1.col1 == value AND row1.col2 == value AND ... ) OR (row2.col1 == value AND row2.col2 == value AND ... ) ORDER BY col DESC;
			string sWhere = "";
			for(row_vector::size_type iRow = 0; iRow < request->get_rows()->size(); iRow++) {
				row rCurrent = request->get_rows()->at(iRow);
				sWhere += "(";
				for(string_string_iterator itChanges = rCurrent.mChanges.begin(); itChanges != rCurrent.mChanges.end(); itChanges++) {
					sWhere += get_where(rCurrent, string(itChanges->first)) + " AND ";
				}
				sWhere += " 1=1) AND ";
			}
			if(sWhere.length() > 0)
				sWhere = sWhere.substr(0, sWhere.length() - 5);

			string sql = "SELECT * FROM \"" + request->get_name() + "\"";
			if(sWhere.length() > 0)
				sql += " WHERE " + sWhere;
			if(order.length() > 0)
				sql += " ORDER BY " + order;

			if(request->get_name().find("INFORMATION_SCHEMA.") == 0) {
				generic_table* gt = (generic_table*) request;
				sql = gt->get_sql();
			}

			return load_rows(request, sql);
		}
		void connection::load_meta_data(table* request) {
			request->get_columns()->clear();
			request->get_labels()->clear();
			request->get_types()->clear();

			string sql = "SELECT * FROM \"" + request->get_name() + "\" WHERE FALSE";
			if(request->get_name().find_first_of("INFORMATION_SCHEMA.", 0) == 0)
				sql = "SELECT * FROM " + request->get_name() + " WHERE FALSE";
						
			mysql_real_query(pMysqlConnection, sql.c_str(), sql.length());
			string temp = mysql_error(pMysqlConnection);
			if(temp.length() > 0)
				sError += "\n\n" + temp;

			MYSQL_RES* pdbResults = mysql_store_result(pMysqlConnection);
			MYSQL_FIELD* pdbFields = mysql_fetch_fields(pdbResults);
			unsigned int uiFieldCnt = mysql_num_fields(pdbResults);
			for(unsigned int cnt = 0; cnt < uiFieldCnt; cnt++) {
				string sColumn = string(pdbFields[cnt].name);
				request->get_columns()->push_back(sColumn);
				(*(request->get_types()))[sColumn] = pdbFields[cnt].type;
				(*(request->get_labels()))[sColumn] =  this->get_user_freidnly_name(string(pdbFields[cnt].name));
			}

			mysql_free_result(pdbResults);
		}
		unsigned long connection::load_rows(table* request, string sql) {
			// execute query					
			mysql_real_query(pMysqlConnection, sql.c_str(), sql.length());
			string temp = mysql_error(pMysqlConnection);
			if(temp.length() > 0)
				sError += "\n\n" + temp;

			MYSQL_RES* pdbResults = mysql_store_result(pMysqlConnection);

			// reset table before load
			request->clear();

			// load field and types
			MYSQL_FIELD* pdbFields = mysql_fetch_fields(pdbResults);
			unsigned int uiFieldCnt = mysql_num_fields(pdbResults);

			// load rows
			MYSQL_ROW row;
			while((row =  mysql_fetch_row(pdbResults))) {				
				string_string_map originals = string_string_map();
				for(unsigned int cnt = 0; cnt < uiFieldCnt; cnt++) {
					char* pszValue = row[cnt];
					if(pszValue)
						originals[pdbFields[cnt].name] = string(pszValue);
				}
				uhdpbs::db::row dbRow = request->create_new_row(originals);
				request->get_rows()->insert(request->get_rows()->end(), dbRow);
			}

			mysql_free_result(pdbResults);

			return request->get_rows()->size();
		}
		string connection::get_where(row r, string column) {
			stringstream ssout = stringstream();
			string sValue = r.get_value(column);

			if(r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_BIT ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_INT24 ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_LONG ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_LONGLONG ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_SHORT ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_TINY
				) {
					ssout << "\"" + column + "\"=" + sValue;
			} else if(r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_DECIMAL ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_DOUBLE ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_FLOAT ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_NEWDECIMAL
				) {
					ssout << "\"" + column + "\"=" + sValue;
			} else if(r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_DATE ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_DATETIME ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_NEWDATE ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_TIME ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_TIMESTAMP ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_YEAR
				) {
					ssout << "\"" + column + "\"='" + sValue + "'";
			} else if(r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_STRING ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_VARCHAR ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_VAR_STRING
				) {
					ssout << "\"" + column + "\"='" + get_escaped(sValue) + "'";
			} /*else if(r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_BLOB ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_LONG_BLOB ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_MEDIUM_BLOB ||
				r.get_table()->get_types()->at(column) == enum_field_types::MYSQL_TYPE_TINY_BLOB ||
				) {
			}*/ else
				throw exception("MySQL type not supported!");

			return ssout.str();
		}
		string connection::get_escaped(string value) {
			if(value.length() == 0)
				return "";
			char pcNew[2048];
			for(int cnt = 0; cnt < sizeof(pcNew); cnt++)
				pcNew[cnt] = 0;

			mysql_escape_string(pcNew, value.c_str(), value.size());
			//mysql_real_escape_string(pMysqlConnection, pcNew, value.c_str(), sizeof(pcNew));
			return string(pcNew);
		}
		string connection::get_sql_value(row r, string column, string value) {
			table* tbl = r.get_table();
			string_fieldtype_map mTypes = *(tbl->get_types());
			enum_field_types ft = mTypes[column];
			if(ft == enum_field_types::MYSQL_TYPE_BIT || ft == enum_field_types::MYSQL_TYPE_INT24 || 
				ft == enum_field_types::MYSQL_TYPE_LONG || ft == enum_field_types::MYSQL_TYPE_LONGLONG || 
				ft == enum_field_types::MYSQL_TYPE_SHORT ||  ft == enum_field_types::MYSQL_TYPE_TINY) {
					return value;
			} else if(ft == enum_field_types::MYSQL_TYPE_DECIMAL || ft == enum_field_types::MYSQL_TYPE_DOUBLE || 
				ft == enum_field_types::MYSQL_TYPE_FLOAT || ft == enum_field_types::MYSQL_TYPE_NEWDECIMAL
				) {
					return value;
			} else if(ft == enum_field_types::MYSQL_TYPE_DATE || ft == enum_field_types::MYSQL_TYPE_DATETIME || 
				ft == enum_field_types::MYSQL_TYPE_NEWDATE || ft == enum_field_types::MYSQL_TYPE_TIME || 
				ft == enum_field_types::MYSQL_TYPE_TIMESTAMP || ft == enum_field_types::MYSQL_TYPE_YEAR
				) {
					return "'" + value + "'";
			} else if(ft == enum_field_types::MYSQL_TYPE_STRING || ft == enum_field_types::MYSQL_TYPE_VARCHAR ||
				ft == enum_field_types::MYSQL_TYPE_VAR_STRING
				) {
					return "'" + get_escaped(value) + "'";
			} else if(ft == enum_field_types::MYSQL_TYPE_BLOB ||
				ft == enum_field_types::MYSQL_TYPE_LONG_BLOB ||
				ft == enum_field_types::MYSQL_TYPE_MEDIUM_BLOB ||
				ft == enum_field_types::MYSQL_TYPE_TINY_BLOB
				) {
					return "'" + get_escaped(value) + "'";
			} else
				throw exception("MySQL type not supported!");
			
		}

		unsigned long connection::save(table* tbl) {
			sError = "";
			
			// load meta data if missing
			load_meta_data(tbl);

			// create where clause
			// TODO : HANDLE DELETES
			unsigned long ulChanges = 0;
			for(row_vector::size_type iRow = 0; iRow < tbl->get_rows()->size(); iRow++) {
				row rCurrent = tbl->get_rows()->at(iRow);
				if(rCurrent.mOriginals.size() >0 && rCurrent.is_deleted())
					ulChanges += save_delete(rCurrent);
				else if(rCurrent.mOriginals.size() == 0 && rCurrent.mChanges.size() > 0)
					ulChanges += save_insert(rCurrent);
				else if(rCurrent.mOriginals.size() > 0 && rCurrent.mChanges.size() > 0)
					ulChanges += save_update(rCurrent);
			}

			tbl->clear();
			return ulChanges;
		}
		unsigned long connection::save_insert(row r) {
			string sColumns = "";
			string sValues = "";
			
			for(string_string_iterator itChanges = r.mChanges.begin(); itChanges != r.mChanges.end(); itChanges++) {
				sColumns += "\"" + itChanges->first + "\", ";
				sValues += get_sql_value(r, itChanges->first, itChanges->second) + ", ";
			}
			if(sColumns.length() > 0)
				sColumns = sColumns.substr(0, sColumns.length() - 2);
			if(sValues.length() > 0)
				sValues = sValues.substr(0, sValues.length() - 2);

			string sql = "INSERT INTO " + r.get_table()->get_name() + "(" + sColumns + ") VALUES (" + sValues + ")";

			return execute(sql);
		}
		unsigned long connection::save_update(row r) {
			string sSets = "";
			for(string_string_iterator itChanges = r.mChanges.begin(); itChanges != r.mChanges.end(); itChanges++)
				sSets += "\"" + itChanges->first + "\"=" + get_sql_value(r, itChanges->first, itChanges->second) + ", ";
			if(sSets.length() > 0)
				sSets = sSets.substr(0, sSets.length() - 2);

			string sWheres = "\"id\"=" + r.get_id();

			string sql = "UPDATE " + r.get_table()->get_name();
			sql += " SET " + sSets;
			sql += " WHERE " + sWheres;
			sql += " LIMIT 1";

			return execute(sql);
		}
		unsigned long connection::save_delete(row r) {
			/*string sWheres = "";
			for(string_string_iterator itOriginals = r.mOriginals.begin(); itOriginals != r.mOriginals.end(); itOriginals++) {
				sWheres += "\"" + itOriginals->first + "\"=" + get_sql_value(r, itOriginals->first, itOriginals->second) + " AND ";
			}
			if(sWheres.length() > 0)
				sWheres = sWheres.substr(0, sWheres.length() - 5);
				*/
			string sql = "DELETE FROM " + r.get_table()->get_name();
			sql += " WHERE id=" + r.get_id();
			sql += " LIMIT 1";

			return execute(sql);
		}
		unsigned long connection::execute(string sql) {
			// execute query					
			mysql_real_query(pMysqlConnection, sql.c_str(), sql.length());
			string temp = mysql_error(pMysqlConnection);
			if(temp.length() > 0)
				sError += "\n\n" + temp;

			return mysql_affected_rows(pMysqlConnection);
		}

		string connection::get_error() { return sError; }
		void connection::clear_error() { sError = ""; }

		string connection::get_user_freidnly_name(string sql) {
			string sRet = string();
			bool bCapitalize = true;
			for(string::size_type cnt = 0; cnt < sql.length(); cnt++) {
				if(sql[cnt] == '_') {
					sRet += " ";
					bCapitalize = true;
				} else if(!isalpha(sql[cnt])) {
					bCapitalize = true;
					sRet += sql[cnt];
				} else if(bCapitalize && isalpha(sql[cnt])) {
					sRet += (char) toupper(sql[cnt]);
					bCapitalize = false;
				} else {
					sRet += sql[cnt];
				}
			}

			return sRet;
		}

	}
}